﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WS1.Models;

namespace WS1.Repository
{
    public class CustomersRepository : IDisposable
    {
        private readonly SqlConnection _sqlConnection;
        public CustomersRepository()
        {
            string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            _sqlConnection = new SqlConnection(connStr);
            _sqlConnection.Open();

        }
        public IEnumerable<Customers> All()
        {
            var sql = @"SELECT  [Customer_id]
      ,[first_name]
      ,[last_name]
      ,[age]
      ,[gender]
      ,[postal_code]
      ,[email]
      ,[phone_number]
      ,[membership_status]
      ,[join_date]
      ,[last_purchase_date]
      ,[total_spending]
      ,[average_order_value]
      ,[frequency]
      ,[preferred_category]
      ,[churned]
  FROM [dbo].[Customers]
";
            var Customers = new List<Customers>();
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var product = new Customers()
                        {
                            Customer_id = reader.GetInt32(0),
                            first_name = reader.GetString(1),
                            last_name = reader.GetString(2),
                            age = reader.GetInt32(3),
                            gender = reader.GetString(4),
                            postal_code = reader.GetString(5),
                            email = reader.GetString(6),
                            phone_number = reader.GetString(7),
                            membership_status = reader.GetString(8),
                            join_date = reader.GetDateTime(9),
                            last_purchase_date = reader.GetDateTime(10),
                            total_spending = reader.GetDecimal(11),
                            average_order_value = reader.GetDecimal(12),
                            frequency = reader.GetString(13),
                            preferred_category = reader.GetString(14),
                            churned = reader.GetBoolean(15)
                        };
                        Customers.Add(product);
                    }
                }
            }
            return Customers;
        }

        public Customers Single(int id)
        {
            var sql = @"SELECT  [Customer_id]
      ,[first_name]
      ,[last_name]
      ,[age]
      ,[gender]
      ,[postal_code]
      ,[email]
      ,[phone_number]
      ,[membership_status]
      ,[join_date]
      ,[last_purchase_date]
      ,[total_spending]
      ,[average_order_value]
      ,[frequency]
      ,[preferred_category]
      ,[churned]
  FROM [BelleCroissantLyormais].[dbo].[Customers]
where [Customer_id]=@Customer_id";
            Customers product = null;
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@Customer_id", id);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        product = new Customers()
                        {
                            Customer_id = reader.GetInt32(0),
                            first_name = reader.GetString(1),
                            last_name = reader.GetString(2),
                            age = reader.GetInt32(3),
                            gender = reader.GetString(4),
                            postal_code = reader.GetString(5),
                            email = reader.GetString(6),
                            phone_number = reader.GetString(7),
                            membership_status = reader.GetString(8),
                            join_date = reader.GetDateTime(9),
                            last_purchase_date = reader.GetDateTime(10),
                            total_spending = reader.GetDecimal(11),
                            average_order_value = reader.GetDecimal(12),
                            frequency = reader.GetString(13),
                            preferred_category = reader.GetString(14),
                            churned = reader.GetBoolean(15)

                        };
                    }
                }

            }
            return product;
        }


        public bool Add(Customers customer)
        {
            //product_id 主键。修改成自增
            var sql = @"INSERT INTO [dbo].[Customers]
           (
			[first_name]
           ,[last_name]
           ,[age]
           ,[gender]
           ,[postal_code]
           ,[email]
           ,[phone_number]
           ,[membership_status]
           ,[join_date]
           ,[last_purchase_date]
           ,[total_spending]
           ,[average_order_value]
           ,[frequency]
           ,[preferred_category]
           ,[churned])
     VALUES
           (@first_name
			,@last_name
			,@age
			,@gender
			,@postal_code
			,@email
			,@phone_number
			,@membership_status
			,@join_date
			,@last_purchase_date
			,@total_spending
			,@average_order_value
			,@frequency
			,@preferred_category
			,@churned)";
            
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@first_name", customer.first_name);
                cmd.Parameters.AddWithValue("@last_name", customer.last_name);
                cmd.Parameters.AddWithValue("@age", customer.age);
                cmd.Parameters.AddWithValue("@gender", customer.gender);
                cmd.Parameters.AddWithValue("@postal_code", customer.postal_code);
                cmd.Parameters.AddWithValue("@email", customer.email);
                cmd.Parameters.AddWithValue("@phone_number", customer.phone_number);


                cmd.Parameters.AddWithValue("@membership_status", customer.membership_status);
                cmd.Parameters.AddWithValue("@join_date", customer.join_date);
                cmd.Parameters.AddWithValue("@last_purchase_date", customer.last_purchase_date);
                cmd.Parameters.AddWithValue("@total_spending", customer.total_spending);
                cmd.Parameters.AddWithValue("@average_order_value", customer.average_order_value);
                cmd.Parameters.AddWithValue("@frequency", customer.frequency);
                cmd.Parameters.AddWithValue("@preferred_category", customer.preferred_category);
                cmd.Parameters.AddWithValue("@churned", customer.churned);





                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public bool Update(int id, Customers customer)
        {
            var sql = @"UPDATE [dbo].[Customers]
   SET			
	   [first_name] =				@first_name
      ,[last_name] =				@last_name
      ,[age] =						@age
      ,[gender] =					@gender
      ,[postal_code] =				@postal_code
      ,[email] =					@email
      ,[phone_number] =				@phone_number
      ,[membership_status] =		@membership_status
      ,[join_date] =				@join_date
      ,[last_purchase_date] =		@last_purchase_date
      ,[total_spending] =			@total_spending
      ,[average_order_value] =		@average_order_value
      ,[frequency] =				@frequency
      ,[preferred_category] =		@preferred_category
      ,[churned] =					@churned
 WHERE  [Customer_id] =	 @Customer_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@Customer_id", id);

                cmd.Parameters.AddWithValue("@first_name", customer.first_name);
                cmd.Parameters.AddWithValue("@last_name", customer.last_name);
                cmd.Parameters.AddWithValue("@age", customer.age);
                cmd.Parameters.AddWithValue("@gender", customer.gender);
                cmd.Parameters.AddWithValue("@postal_code", customer.postal_code);
                cmd.Parameters.AddWithValue("@email", customer.email);
                cmd.Parameters.AddWithValue("@phone_number", customer.phone_number);


                cmd.Parameters.AddWithValue("@membership_status", customer.membership_status);
                cmd.Parameters.AddWithValue("@join_date", customer.join_date);
                cmd.Parameters.AddWithValue("@last_purchase_date", customer.last_purchase_date);
                cmd.Parameters.AddWithValue("@total_spending", customer.total_spending);
                cmd.Parameters.AddWithValue("@average_order_value", customer.average_order_value);
                cmd.Parameters.AddWithValue("@frequency", customer.frequency);
                cmd.Parameters.AddWithValue("@preferred_category", customer.preferred_category);
                cmd.Parameters.AddWithValue("@churned", customer.churned);


                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public bool RemoveSingle(int id)
        {
            var sql = @"DELETE [dbo].[products]
                         WHERE  [product_id] = @product_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@product_id", id);
                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public void Dispose()
        {
            _sqlConnection.Close();
            _sqlConnection.Dispose();
        }
    }
}